Overview:

Connecting to a mainstream database server and performing SQL operations is very common to Python applications. There are several database drivers or DB APIs developed for connecting to MySQL Server and other database servers from a Python Application. The focus here is to connect to a MySQL Server and insert rows into a database table using PyMySQL.

PyMySQL is developed using all Python. It adheres to the Python Database API specification. PyMySQL comes with an MIT license.

Inserting rows into a MySQL database table using Python:

The Python Database API specification makes it very simple to connect to any database server and perform database operations using SQL.

The steps to insert a number of rows onto a MySQL database table are:

Create a connection object using PyMySQL module.
Obtaining a connection object involves calling the connect factory method with several database parameters like
- IP address or Host Name on which the MySQL database server is running
- The database user name
- Password for the user
- Name of the database to connect to
- Encoding used
- Cursor type and any other required parameters
Once a connection object is obtained, a cursor object needs to be created to execute any SQL statement over the database connection.
Upon calling the execute() method by providing the SQL statement as a string - any SQL operation is performed on the database server.
The same way, an SQL INSERT statement can be executed and new rows can be inserted on to the database.
The database table can be queried and the rows from the tables can be printed on to the console.

Example:

The Python example code connects to a MySQL database and creates a new table and inserts a row into the table. It prints the id/primary-key of the last inserted row. The Python example queries the MySQL table and prints the query results.

# Example Python program to insert rows into a MySQL database table

# import the mysql client for python
import pymysql

# Create a connection object
dbServerName = "127.0.0.1"
dbUser = "root"
dbPassword = ""
dbName = "test"
charSet = "utf8mb4"

connectionObject = pymysql.connect(host=dbServerName, user=dbUser, password=dbPassword,
db=dbName, charset=charSet)

try:
# Create a cursor object
cursorObject = connectionObject.cursor()

# SQL string to create a MySQL table
sqlCreateTableCommand = "CREATE TABLE Employee(id int(11) AUTO_INCREMENT PRIMARY KEY, LastName varchar(32), FirstName varchar(32), DepartmentCode int)"

# Execute the sqlQuery
cursorObject.execute(sqlCreateTableCommand)

# List the tables using SQL command
sqlShowTablesCommand = "show tables"

# Execute the SQL command
cursorObject.execute(sqlShowTablesCommand)

#Fetch all the rows - from the command output
rows = cursorObject.fetchall()
for row in rows:
print(row)

# Insert rows into the MySQL Table
insertStatement = "INSERT INTO Employee (id, LastName, FirstName, DepartmentCode) VALUES (1,\"Albert\",\"Einstein\",10)"
cursorObject.execute(insertStatement)

# Get the primary key value of the last inserted row
print("Primary key id of the last inserted row:")
print(cursorObject.lastrowid)

# SQL Query to retrive the rows
sqlQuery = "select * from Employee"

#Fetch all the rows - for the SQL Query
cursorObject.execute(sqlQuery)
rows = cursorObject.fetchall()

for row in rows:
print(row)

except Exception as e:
print("Exeception occured:{}".format(e))

finally:
connectionObject.close()

Output:

('A',)

('B',)

('Employee',)

('Student',)

('exper',)

(1, 'Albert', 'Einstein', 10)

Inserting Rows into MySQL database table using Python and PyMySQL

Overview:

Inserting rows into a MySQL database table using Python:

Example:

Output: